import string
import pandas
from app import useDB

def read_excel(path):
    try:
        work_book = pandas.read_excel(path, engine='openpyxl')
        data_list = work_book.values.tolist()
        title_list = list(work_book)
        list_temp = []
        for i in range(len(data_list)):
            data_dict = dict()
            for j in range(len(title_list)):
                data_dict[title_list[j]] = data_list[i][j]
            list_temp.append(data_dict)
        new_test_case_2(list_temp)
        lag = 1
    except:
        lag = 0
    return lag

def new_test_case_2(datalist):
    con=useDB.useDB()
    for data in datalist:
        sql = string.Template(
            '''
            insert into test_case (module,name,steps,description,isPublicFunction) values 
            ("$module","$name","$steps","$description",$isPublic);
            ''')
        sql = sql.substitute(name=data["name"], module=data["module"], steps=["steps"], description=data["description"], isPublic=data["isPublicFunction"])
        con.insert(sql)

if __name__ == '__main__':
    read_excel("/Users/gabriel/Desktop/test.xlsx")